﻿Imports System.Data.SqlClient
Imports System.Data

Public Class cCourt

    Dim con As New SqlConnection(strcon)

    Public Function select_Court() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT CourtID,CourtName FROM tblCourt WHERE Active=1")
        Return dt
    End Function

    Public Function select_Court_byID(ByVal CourtID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT CourtID, CourtName FROM tblCourt WHERE Active=1 AND CourtID=N'" & CourtID & "'")
        Return dt
    End Function

    Public Function select_Court_byKeyword(ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT CourtID,CourtName FROM tblCourt WHERE Active=1 AND LOWER(CourtName) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function insert_Court(ByVal CourtName As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblCourt(CourtName) VALUES(@CourtName)"
        With cmd.Parameters
            .AddWithValue("@CourtName", CourtName)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function update_Court(ByVal CourtID As Long, ByVal CourtName As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblCourt SET CourtName=@CourtName WHERE CourtID=@CourtID"
        With cmd.Parameters
            .AddWithValue("@CourtID", CourtID)
            .AddWithValue("@CourtName", CourtName)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_Court(ByVal CourtID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "DELETE FROM tblCourt WHERE CourtID=@CourtID"
        With cmd.Parameters
            .AddWithValue("@CourtID", CourtID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

End Class
